/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package com.model.service;

import com.model.domain.tiempo.Mes;
import com.model.domain.tiempo.MesEnum;
import com.model.domain.tiempo.Periodo;
import com.model.domain.tiempo.ProgramacionPeriodo;
import com.model.domain.ubicacion.Conglomerado;
import com.model.domain.ubicacion.Odei;
import com.model.domain.ubicacion.AreaGeografica;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.List;
import util.www.jdbc.SeteadorJdbc;
import util.www.sql.WhereSqlDinamicoCond;


/**
 *
 * @author Rdelacruz
 */
public class ConglomeradoService 
extends AbstractService
{
    
    
    private OdeiService odeiService;
    private DistritoService distritoService;
    private CentroPobladoService centroPobladoService;
    private ProgramacionPeriodoService programacionPeriodoService;
    private PeriodoService periodoService;
    
    public Conglomerado findByCodigo(Connection con, String codigo, SeteadorJdbc<Conglomerado> seteador) throws SQLException {
        Statement st = null;
        try {                        
            st = con.createStatement();
            String sql = "select * from Campo_Conglomerado"
                    + " where Conglome='"+codigo+"'";
            ResultSet rs = st.executeQuery(sql);            
            if(rs.next()) {
                Conglomerado obj = new Conglomerado();
                obj.setCodigo(rs.getString("Conglome"));                
                seteador.setear(con, rs, obj);                
                return obj;
            }
        }
        finally {
            if(st!=null) st.close();
        }
        return null;
    }
    
    public void setearSede(ResultSet rs, Conglomerado obj) throws SQLException {
        obj.setAreaGeografica(AreaGeografica.findByCodigo(rs.getString("sede")));
    }
    
    public void setearAnhoMesPeriodoConFechaInicio(Connection con, ResultSet rs, Conglomerado obj) throws SQLException {
        setearAnhoMesPeriodo(rs,obj);
        periodoService.setearFechaInicioByAnhoMesPeriodo(con, obj.getPeriodo(), obj.getAnho(),obj.getMes().getCodigo(), obj.getPeriodo().getCodigo());
    }
    
    
    public void setearAnhoMesPeriodo(ResultSet rs, Conglomerado obj) throws SQLException {
        obj.setAnho(rs.getString("Año"));

        Mes m = new Mes();
        m.setCodigo(rs.getString("Mes"));
        m.setNombre(MesEnum.getNombreByCodigo(m.getCodigo()));
        obj.setMes(m);

        Periodo p = new Periodo();
        p.setCodigo(rs.getString("Periodo"));
        obj.setPeriodo(p);
    }
    
    public void setearDistrito(Connection con, ResultSet rs, Conglomerado obj) throws SQLException {
        String ubigeo = rs.getString("Dpto")+rs.getString("Prov")+rs.getString("Dist");
        obj.setDistrito(distritoService.findByUbigeo(con, ubigeo));
    }
    public void setearOdei(Connection con, ResultSet rs, Conglomerado obj) throws SQLException {
        obj.setOdei(odeiService.findByCodigo(con, rs.getString("Odei")));
    }
    
    public void setearTipoSeleccion(ResultSet rs, Conglomerado obj) throws SQLException {
        obj.setTipoSeleccion(rs.getString("TipoSeleccion"));
    }
    
    public void setearCentrosPoblados(Connection con, Conglomerado obj) throws SQLException {
        obj.setCentrosPoblados(centroPobladoService.findByConglomerado(con,obj.getCodigo()));
    }
    
    public List<Conglomerado> findByFilter(Connection con, Conglomerado filtro) throws SQLException {
        List<Conglomerado> lista = new ArrayList<Conglomerado>();
        Statement st = null;
        try {                        
            st = con.createStatement();
            WhereSqlDinamicoCond w = new WhereSqlDinamicoCond();
            
            if(filtro.getOdei()!=null)
                w.agregarString("odei", filtro.getOdei().getCodigo());
            w.agregarString("año", filtro.getAnho());
            
            if(filtro.getMes()!=null)
                w.agregarString("mes", filtro.getMes().getCodigo());
            
            if(filtro.getPeriodo()!=null)
                w.agregarString("periodo", filtro.getPeriodo().getCodigo());
            
            String sql = "select * from Campo_Conglomerado"
                    + " "+w.getAnd()
                    + " order by Conglome";
            ResultSet rs = st.executeQuery(sql);            
            while(rs.next()) {
                Conglomerado obj = new Conglomerado();
                obj.setCodigo(rs.getString("Conglome"));
                setearTipoSeleccion(rs,obj);
                lista.add(obj);
            }
        }
        finally {
            if(st!=null) st.close();
        }
        return lista;
    }

    public List<Conglomerado> findAll_codigo(Connection con) throws SQLException {
        List<Conglomerado> lista = new ArrayList<Conglomerado>();
        Statement st = null;
        try {                        
            st = con.createStatement();
            String sql = "select Conglome from Campo_Conglomerado"
                    + " order by Conglome";
            ResultSet rs = st.executeQuery(sql);            
            while(rs.next()) {
                Conglomerado obj = new Conglomerado();
                obj.setCodigo(rs.getString(1));
                lista.add(obj);
            }
        }
        finally {
            if(st!=null) st.close();
        }
        return lista;
    }

    
    
    public Conglomerado findConPeriodoByCodigo(Connection con, String codigo) throws SQLException, ParseException {
        Conglomerado c = findAnhoMesPeriodoByCodigo(con,codigo);
        if(c!=null) {
            ProgramacionPeriodo pp = programacionPeriodoService.findByAnhoMes(con, c.getAnho(), c.getMes().getCodigo());
            Periodo p = pp.getPeriodoByCodigo(c.getPeriodo().getCodigo());            
            c.getPeriodo().setFechaInicio(p.getFechaInicio());
        }            
        return c;
    }
    
    
    
    public Conglomerado findByCodigo(Connection con, String codigo) throws SQLException {
        Statement st = null;
        try {                        
            st = con.createStatement();
            String sql = "select * from Campo_Conglomerado"
                    + " where Conglome='"+codigo+"'";
            ResultSet rs = st.executeQuery(sql);            
            if(rs.next()) {
                Conglomerado obj = new Conglomerado();
                obj.setCodigo(rs.getString("Conglome"));
                setearTipoSeleccion(rs,obj);
                
                Odei odei = new Odei();
                odei.setCodigo(rs.getString("Odei"));
                obj.setOdei(odei);
                return obj;
            }
        }
        finally {
            if(st!=null) st.close();
        }
        return null;
    }
    
    public Conglomerado findAnhoMesPeriodoByCodigo(Connection con, String codigo) throws SQLException {
        Statement st = null;
        try {                        
            st = con.createStatement();
            String sql = "select * from Campo_Conglomerado where conglome='"+codigo+"'";
            ResultSet rs = st.executeQuery(sql);            
            if(rs.next()) {
                Conglomerado obj = new Conglomerado();
                setearAnhoMesPeriodo(rs,obj);      
                setearTipoSeleccion(rs,obj);                
                return obj;
            }
        }
        finally {
            if(st!=null) st.close();
        }
        return null;
    }
    
    
    
    public Conglomerado findDistritoByCodigo(Connection con, String codigo) throws SQLException {
        Statement st = null;
        try {                        
            st = con.createStatement();
            String sql = "select * from Campo_Conglomerado where conglome='"+codigo+"'";
            ResultSet rs = st.executeQuery(sql);            
            if(rs.next()) {
                Conglomerado obj = new Conglomerado();
                obj.setCodigo(rs.getString("conglome"));
                setearDistrito(con,rs,obj);
                setearTipoSeleccion(rs,obj);
                
                Periodo p = new Periodo();
                p.setCodigo(rs.getString("Periodo"));
                obj.setPeriodo(p);
                return obj;
            }
        }
        finally {
            if(st!=null) st.close();
        }
        return null;
    }
    
    
    public List<Integer> findAnhos(Connection con) throws SQLException {
        List<Integer> lista = new ArrayList<Integer>();
        Statement st = null;
        try {                        
            st = con.createStatement();
            String sql = "select distinct año from Campo_Conglomerado";
            ResultSet rs = st.executeQuery(sql);            
            while(rs.next()) {
                lista.add(new Integer(rs.getString(1)));
            }
        }
        finally {
            if(st!=null) st.close();
        }
        return lista;
    }

    public DistritoService getDistritoService() {
        return distritoService;
    }

    public void setDistritoService(DistritoService distritoService) {
        this.distritoService = distritoService;
    }

    public CentroPobladoService getCentroPobladoService() {
        return centroPobladoService;
    }

    public void setCentroPobladoService(CentroPobladoService centroPobladoService) {
        this.centroPobladoService = centroPobladoService;
    }

    public ProgramacionPeriodoService getProgramacionPeriodoService() {
        return programacionPeriodoService;
    }

    public void setProgramacionPeriodoService(ProgramacionPeriodoService programacionPeriodoService) {
        this.programacionPeriodoService = programacionPeriodoService;
    }

    public OdeiService getOdeiService() {
        return odeiService;
    }

    public void setOdeiService(OdeiService odeiService) {
        this.odeiService = odeiService;
    }

    

    public PeriodoService getPeriodoService() {
        return periodoService;
    }

    public void setPeriodoService(PeriodoService periodoService) {
        this.periodoService = periodoService;
    }

    
    

}
